/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sun.courier.dal;

import com.sun.courier.entity.CourierManage;
import com.sun.courier.entity.Courier;
import com.sun.courier.entity.Department;
import com.sun.courier.entity.Employee;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author NamBH
 */
public class GetStatusCourierDAL {

    private final static String GET_ALL = "select * from Courier ORDER BY CourierId desc";
    //private final static String GET_ALL_MANAGEMENT = "select * from CourierManage ORDER BY CourierId desc";
    private final static String SEARCH_COURIERNAME = "select * from Courier where CourierName like ? "
            //            + "or CustomerName like ? "
            //            + "or Phone like ? "
            //            + "or DepartLocation like ? "
            //            + "or Destination like ? "
            + "ORDER BY CourierId desc";
    private final static String SEARCH_CUSTEMERNAME = "select * from Courier where CustomerName like ? "
            + "ORDER BY CourierId desc";
    private final static String SEARCH_PHONE = "select * from Courier where Phone like ? "
            + "ORDER BY CourierId desc";
//    private final static String GET_ALL_STATUS = "select CourierManage.Status,CourierManage.CurrentPosition,CourierManage.Description,CourierManage.UpdateTime from Courier join CourierManage ON Courier."+"?"+" = "+ "CourierManage."+"?";
    private final static String GET_ALL_STATUS = "select * from CourierManage where CourierId=? ";
    private final static String GET_ALL_ById = "select * from Courier where CourierId=? ";
    private final static String GET_ALL_ABOUT_EMPLOYEE = "select * from Employee where EmpId=? ";
    private DataAccessHelper dah = new DataAccessHelper();
    private Connection con;

    public ArrayList<Courier> getAll() {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                ResultSet rs = con.prepareStatement(GET_ALL).executeQuery();
                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setReceivingCustomer("ReceivingCustomer");
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));


                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }
    
    public ArrayList<Courier> getById(int courierId) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(GET_ALL_ById);
                pre.setInt(1, courierId);
                ResultSet rs = pre.executeQuery();
                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }
    

    public ArrayList<CourierManage> getByIdStatus(int courierId) {
        ArrayList<CourierManage> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(GET_ALL_STATUS);
                pre.setInt(1, courierId);
                ResultSet rs = pre.executeQuery();
                try {
                    while (rs.next()) {
                        CourierManage obj = new CourierManage();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setReceiver(rs.getInt("Receiver"));
                        obj.setSender(rs.getInt("Sender"));
                        obj.setDispatcher(rs.getInt("Dispatcher"));
                        obj.setStatus(rs.getInt("Status"));
                        obj.setCurrentPosition(rs.getInt("CurrentPosition"));
                        obj.setDescription(rs.getString("Description"));
                        obj.setUpdatetime(rs.getString("Updatetime"));

                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }

        return objs;
    }

        public ArrayList<Employee> getByIdEmployee(int EmpId) {
        ArrayList<Employee> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(GET_ALL_ABOUT_EMPLOYEE);
                pre.setInt(1, EmpId);
                ResultSet rs = pre.executeQuery();
                try {
                    while (rs.next()) {
                        Employee obj = new Employee();
                        obj.setEmployeeName(rs.getString("EmployeeName"));
                        obj.setAddress(rs.getString("Address"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setEmail("Email");
                        obj.setImage("Image");
                        
                        
                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }

        return objs;
    }


    public ArrayList<Courier> searchCourierName(String strSeach) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(SEARCH_COURIERNAME);
                pre.setString(1, strSeach);
//                pre.setString(2, "%"+strSeach+"%");
//                pre.setString(3, "%"+strSeach+"%");
//                pre.setString(4, "%"+strSeach+"%");
//                pre.setString(5, "%"+strSeach+"%");
                ResultSet rs = pre.executeQuery();

                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));


                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }

    public ArrayList<Courier> searchCustemer(String strSeach) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(SEARCH_CUSTEMERNAME);
                pre.setString(1, strSeach);
//                pre.setString(2, "%"+strSeach+"%");
//                pre.setString(3, "%"+strSeach+"%");
//                pre.setString(4, "%"+strSeach+"%");
//                pre.setString(5, "%"+strSeach+"%");
                ResultSet rs = pre.executeQuery();

                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));

                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }

    public ArrayList<Courier> searchPhone(String strSeach) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(SEARCH_PHONE);
                pre.setString(1, strSeach);
//                pre.setString(2, "%"+strSeach+"%");
//                pre.setString(3, "%"+strSeach+"%");
//                pre.setString(4, "%"+strSeach+"%");
//                pre.setString(5, "%"+strSeach+"%");
                ResultSet rs = pre.executeQuery();

                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));


                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }
}
